package com.iis.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import com.iis.databaseutil.DatabaseConnection;
import com.iis.entity.Salesman;
import com.iis.entity.Person;

public class SalesmanDAO {
private static final SalesmanDAO salesmanDAO = new SalesmanDAO();
	
	private SalesmanDAO(){
		
	}
	
	public static SalesmanDAO getInstances(){
		return salesmanDAO;
	}
	
	Connection koneksiKu = null;

	PreparedStatement psInsertSalesman = null;

	String sqlInsertSalesman = "INSERT INTO Salesman(Employee_No, Sales_Target, Person_idPerson) "
			+ "VALUES (?,?,?) ";

	public Salesman save(Salesman newSalesman) throws SQLException {
		if (newSalesman == null) {
			return null;
		}
		koneksiKu = DatabaseConnection.getDataConnection(false);
		int index = 1;
		try {
			
			Person newPerson = newSalesman;
			
			PersonDAO myPersonDAO = PersonDAO.getInstance();
			
			newPerson = myPersonDAO.save(newPerson);

			psInsertSalesman = koneksiKu.prepareStatement(sqlInsertSalesman,
					Statement.RETURN_GENERATED_KEYS);
			
			//generate employee no
			Calendar myCal = Calendar.getInstance();
			myCal.setTime(new java.util.Date());
			String newEmployNo = "EMP" + "-" + myCal.YEAR + "-" + myCal.MONTH + "-" + newPerson.getPersonID();
			newSalesman.setEmployeeNo(newEmployNo);
			
			psInsertSalesman.setString(index++, newSalesman.getEmployeeNo());
			psInsertSalesman.setDouble(index++, newSalesman.getSalesTarget());
			psInsertSalesman.setLong(index++,	 newSalesman.getPersonID());
			int jumlahRow = psInsertSalesman.executeUpdate();
			if (jumlahRow == 0) {
				throw new SQLException("Gagal menyimpan person");
			}
			try (ResultSet generatedKeys = psInsertSalesman.getGeneratedKeys()) {
				if (generatedKeys.next()) {
					newSalesman.setSalesmanID(generatedKeys.getLong(1));
					koneksiKu.commit();
					return newSalesman;
				} else {
					throw new SQLException("Gagal menyimpan person");
				}
			} catch (Exception e) {
				koneksiKu.rollback();
			}
			

		} catch (SQLException e) {
			e.printStackTrace();
			koneksiKu.rollback();
		}
		return null;
	}
	
	PreparedStatement psSelectAllSalesman = null;

	String sqlGetAllSalesman = "Select * from salesman s, person p where s.person_idPerson = p.idPerson and s.deleted = 'N' and p.deleted = 'N'";
	
	public ArrayList<Salesman> getAll(){
		koneksiKu = DatabaseConnection.getDataConnection();
		int index = 1;
		ArrayList<Salesman> arrSalesman = new ArrayList<Salesman>();
		try {

			psSelectAllSalesman = koneksiKu.prepareStatement(sqlGetAllSalesman);
			ResultSet rs = psSelectAllSalesman.executeQuery();

			while (rs.next()) {
				long idSalesman = rs.getLong("idSalesman");
				String empNo = rs.getString("Employee_No");
				double targetSales = rs.getDouble("Sales_Target");
				long idPerson = rs.getLong("idPerson");
				String name = rs.getString("Name");
				String address = rs.getString("Address");
				
				Salesman salesman = new Salesman();
				salesman.setSalesmanID(idSalesman);
				salesman.setEmployeeNo(empNo);;
				salesman.setSalesTarget(targetSales);
				salesman.setPersonID(idPerson);
				salesman.setName(name);
				salesman.setAddress(address);

				arrSalesman.add(salesman);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return arrSalesman;
	}
}
